package DBLayer;

import ModelLayer.*;
import java.sql.*;
import java.util.ArrayList;

public class DBTrainer implements DBTrainerIF
{
	private Connection con;
    
    public DBTrainer() 
    {
      con = DBConnection.getInstance().getDBcon();
    }
    
    public ArrayList<Trainer> getAllTrainers(boolean retriveAssociation)
    {
        return miscWhere("", retriveAssociation);
    }

    public Trainer searchTrainerID(int TrainerID, boolean retriveAssociation)
    {   
    	String wClause = "TrainerID = " + TrainerID + "";
        return singleWhere(wClause, retriveAssociation);
    }
	
	private ArrayList<Trainer> miscWhere(String wClause, boolean retrieveAssociation)
	{
		ResultSet results;
		ArrayList<Trainer> TrainerList = new ArrayList<Trainer>();	
		
		String query =  buildQuery(wClause);
  
  		try
  		{
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 		results = stmt.executeQuery(query);
	 		
			while( results.next() )
			{
				Trainer tObj = new Trainer();
				tObj = buildTrainer(results);
				TrainerList.add(tObj);
			}
			stmt.close();
		}
	 	catch(Exception e)
	 	{
	 		System.out.println("Query exception - select: "+e);
			e.printStackTrace();
	 	}
		return TrainerList;
	}
	
	private Trainer singleWhere(String wClause, boolean retrieveAssociation)
	{
		ResultSet results;
		Trainer tObj = new Trainer();
	    String query =  buildQuery(wClause);
//        System.out.println(query);
		try
		{
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 		results = stmt.executeQuery(query);
	 		
	 		if( results.next() )
	 		{
	 			tObj = buildTrainer(results);
	 			stmt.close();
			}
		}	
	 	catch(Exception e)
	 	{
	 		System.out.println("Query exception: "+e);
	 	}
		return tObj;
	}

	private String buildQuery(String wClause)
	{
	    String query="SELECT cp.PersonID, cp.FirstName, cp.LastName, cp.Address, cp.PostalCode, cp.City, cp.PhoneNr, cp.MobileNr, cp.Email, t.TrainerID, t.CPR, t.Initials, g.GrownupID FROM Trainer t, Grownup g, Person cp";
	    query=query+" WHERE t.GrownupID = g.GrownupID AND g.PersonID = cp.PersonID";
		if (wClause.length()>0)
			query=query+" AND " + wClause;
		return query;
	}

	private Trainer buildTrainer(ResultSet results)
      {   
		Trainer tObj = new Trainer();
          try
          { /* PersonID, FirstName, LastName, Address, PostalCode, City, PhoneNr, MobileNr, Email, TrainerID, CPR, Initials, GrownupID */
        	  tObj.setPersonID(results.getInt(1));
        	  tObj.setfName(results.getString(2));
        	  tObj.setlName(results.getString(3));
        	  tObj.setAddress(results.getString(4));
        	  tObj.setPostalCode(results.getInt(5));
        	  tObj.setCity(results.getString(6));
        	  tObj.setPhoneNr(results.getString(7));
        	  tObj.setMobileNr(results.getString(8));
        	  tObj.setEmail(results.getString(9));
        	  tObj.setTrainerID(results.getInt(10));
        	  tObj.setCPR(results.getString(11));
        	  tObj.setInitials(results.getString(12));
        	  tObj.setGrownupID(results.getInt(13));
          }
         catch(Exception e)
         {
             System.out.println("error in building the Trainer object");
         }
         return tObj;
      }

}
